package my.market.jdbc.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.catalina.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.impl.NewPooledConnection;

import my.market.jdbc.utils.C3p0Utils;
import my.market.jdbc.dataClass.Users;
//import java.sql.Connection;
//import java.sql.ResultSet;
//import java.sql.Statement;
//import java.text.SimpleDateFormat;
//import java.util.ArrayList;
//import my.market.jdbc.dataClass.Users;
//import my.market.jdbc.utils.C3p0Utils;

public class UsersDao {
	//searchAll
	public List<Users> searchAll() throws SQLException{
		try {
			QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
			String sql = "select * from users";
			List<Users> list = (List<Users>) runner.query(sql, new BeanListHandler(Users.class)) ;
			return list;
		}catch (SQLException e) {
			e.printStackTrace();// TODO: handle exception
		}
		return null;
	}
	//findUser
	public Users findUser(String mail) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users where mail=?";
		Users user = (Users) runner.query(sql, new BeanHandler(Users.class), new Object[] {mail});
		return user;
	}
	public Users findUserByName(String name) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users where name=?";
		Users user = (Users) runner.query(sql, new BeanHandler(Users.class), new Object[] {name});
		return user;
	}
	//insertUser
	public boolean insert(Users user) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "insert into users (mail, name, password, sex, status, money, reg_time, phone, description) values (?,?,?,?,?,?,?,?,?)";
		int num = runner.update(sql, new Object[] {user.getMail(), user.getName(), user.getPassword(), user.getSex(),
				user.getStatus(), user.getMoney(), user.getReg_time(), user.getPhone(), user.getDescription()
		});
		if(num > 0) {
			return true;
		}
		return false;
	}
	//deleteUser
	public boolean deleteUser(String mail) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "delete from users where mail=?";
		int num = runner.update(sql, mail);
		if(num > 0) {
			return true;
		}
		return false;
	}
	//updateUser
	public boolean updateUser(Users user) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "update users set name=?, password=?, sex=?, status=?, money=?, reg_time=?, phone=?, description=? where mail=?";
		int num = runner.update(sql, new Object[] {user.getName(), user.getPassword(), user.getSex(),
				user.getStatus(), user.getMoney(), user.getReg_time(), user.getPhone(), user.getDescription(), user.getMail()});
		if (num > 0) {
			return true;
		}
		return false;
	}
	
}
